﻿CREATE PROCEDURE [dbo].[spPackageInventory]
	@OwnerID int,
	@LocationID int
AS
set nocount on

-- Get the list of paid orders that are not locked and change the status to InProcess
update T1 set OrderStatusCode = 'O'
from tblOrderHeader T1
	left outer join tblCustomerLock T2 on T1.CustomerID = T2.CustomerID
where T1.OrderStatusCode = 'E'
	and T2.CustomerID is null
	and T1.OwnerID = @OwnerID

-- Delete the packages which have not been printed yet.
update T2 set PackageID = null
from tblPackage T1
	join tblInventoryTransaction T2 on T1.PackageID = T2.PackageID
where T2.InventoryTransactionStatusCode in ('A', 'X', 'L')

delete from T1
from tblPackage T1
	LEFT outer join tblInventoryTransaction T2 on T1.PackageID = T2.PackageID
where T2.PackageID is null

declare @ToPack table ( AddressID int, PersonID int, InventoryTransactionID int, CarrierCode char(1), CustomerID int )

insert into @ToPack ( AddressID, PersonID, InventoryTransactionID, CarrierCode, CustomerID )
select T1.ShipTo_AddressID, T1.ShipTo_PersonID, T2.InventoryTransactionID, coalesce( T1.PreferredCarrierCode, T4.PreferredCarrierCode, T5.PreferredCarrierCode ), T1.CustomerID
from tblOrderHeader T1
	join tblInventoryTransaction T2 on T1.OrderID = T2.OrderID
	join tblInventory T3 on T2.InventoryID = T3.InventoryID
	join tblProduct T4 on T3.ProductID = T4.ProductID
	join tblOwner T5 on T1.OwnerID = T5.OwnerID
where T1.OrderStatusCode = 'O'
	and T2.InventoryTransactionStatusCode = 'A'
	and T1.OwnerID = @OwnerID
	and T3.LocationID = @LocationID

-- Set the country specific carrier for this warehouse.
update T1 set CarrierCode = T3.CarrierCode
from @ToPack T1
	join tblAddress T2 on T1.AddressID = T2.AddressID
	join tblCountryCarrier T3 on T2.CountryID = T3.CountryID and T3.LocationID = @LocationID
where T3.CarrierCode is not null

-- Create new package records for inventory transactions that are in the Allocated state and the order status is in InProcess
-- Group by the AddressID and the carrier code.
declare c1 cursor local for select AddressID, PersonID, CarrierCode, CustomerID
	from @ToPack
	group by AddressID, PersonID, CarrierCode, CustomerID
declare @AddressID int
declare @PersonID int
declare @CustomerID int
declare @CarrierCode char(1)
open c1
fetch next from c1 into @AddressID, @PersonID, @CarrierCode, @CustomerID
while @@FETCH_STATUS = 0
begin
	declare @PackageID int

	insert into tblPackage ( CreateDate, CarrierCode, PersonID, AddressID, OwnerID )
	values ( GETDATE(), @CarrierCode, @PersonID, @AddressID, @OwnerID )

	set @PackageID = SCOPE_IDENTITY()

	update T1 set PackageID = @PackageID
	from tblInventoryTransaction T1
		join @ToPack T2 on T1.InventoryTransactionID = T2.InventoryTransactionID
	where T2.AddressID = @AddressID 
		and T2.PersonID = @PersonID
		and T2.CarrierCode = @CarrierCode

	update T1 set [Weight] = T2.[Weight]
	from tblPackage T1
		join ( select T2.PackageID, sum( (0 - T2.Qty) * T4.[Weight] ) as [Weight]
				from tblInventoryTransaction T2 
					join tblInventory T3 on T2.InventoryID = T3.InventoryID
					join tblProduct T4 on T3.ProductID = T4.ProductID
				group by T2.PackageID ) as T2 on T1.PackageID = T2.PackageID
	where T1.PackageID = @PackageID

	exec spBuildSearchKeys @CustomerID

	fetch next from c1 into @AddressID, @PersonID, @CarrierCode, @CustomerID
end

return 0